{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WORKING WITH SQL "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install ipython-sql #-- install sq;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "The sql extension is already loaded. To reload it, use:\n",
      "  %reload_ext sql\n"
     ]
    }
   ],
   "source": [
    "#load sql to notebook\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Connected: @testdb.sqlite#'"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a database connection\n",
    "%sql sqlite:///testdb.sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "--- # The sql magic tells jupyter to interprete this cell as SQL\n",
    "--- # In SQL comments begin with \"---\" (we add # to take advantage of jupyter's syntax highlighting)\n",
    "\n",
    "--- #Since we are starting a new example, let's delete any existing table\n",
    "DROP TABLE IF EXISTS customers;\n",
    "DROP TABLE IF EXISTS products;\n",
    "DROP TABLE IF EXISTS orders;\n",
    "\n",
    "--- #Now let's make our tables\n",
    "CREATE TABLE customers(\n",
    "    id     INTEGER  NOT NULL PRIMARY KEY,\n",
    "    name   TEXT NOT NULL,\n",
    "    billing_address  TEXT NOT NULL\n",
    ");\n",
    "\n",
    "CREATE TABLE products(\n",
    "    id INTEGER PRIMARY KEY NOT NULL,\n",
    "    price INTEGER NOT NULL   \n",
    ");\n",
    "\n",
    "CREATE TABLE orders(\n",
    "    id INTEGER NOT NULL,\n",
    "    customer_id INTEGER NOT NULL,\n",
    "    product_id NUMBER NOT NULL,\n",
    "    delivery_address TEXT NOT NULL,\n",
    "    FOREIGN KEY (customer_id) REFERENCES customers(id),\n",
    "    FOREIGN KEY (product_id) REFERENCES products(id)\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### --- # we would only be able to create rows with customers id whose id is also present in the customer's table\n",
    "\n",
    "#### --- # by doing this we are setting up a relationship between customers table and orders table\n",
    " \n",
    "#### --- # we would only be able to create rows with products id whose id also present in the product's table\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_address</th>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "--- # our tables are inititally empty but we have defined the schema\n",
    "---- # We can inspect the table by using SELECT\n",
    "SELECT * FROM orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### INSERTING DATA INTO TABLES\n",
    "--- # We have to be careful to do this in a certain order, when we defined the orders table,\n",
    "we defined a relationsheip between the customer_id and product_id attributes and the id attributes in the customer and product tables respectively. We can only INSERT data into the orders tables once the appropriate customers and products exist in thier tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n",
      "5 rows affected.\n",
      "2 rows affected.\n",
      "2 rows affected.\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "--# starting with customers\n",
    "INSERT INTO customers (id, name, billing_address)\n",
    "    VALUES (435, 'Omar', 'Berlin, Germany'), (5692, 'Stuart', 'Dover, UK'), (6127,\n",
    "                                                                             'Vidhya', 'Mumbai, India');\n",
    "INSERT INTO products (id, price)\n",
    "    VALUES (103, 6.95), (4028, 35.5), (3158, 101.99), (2561, 21.35), (89, 16.95);\n",
    "\n",
    "INSERT INTO orders (id, customer_id, product_id, delivery_address)\n",
    "    VALUES (62533, 435, 103, 'Munich, Germany'), (62353, 435, 4028, 'Tunis, Tunisia');\n",
    "    \n",
    "INSERT INTO orders (id, customer_id, product_id, delivery_address)\n",
    "    VALUES (64598, 5692, 103, 'Dover, UK'), (65271, 5692, 103, 'Dover, UK');\n",
    "    \n",
    "INSERT INTO orders (id, customer_id, product_id, delivery_address)\n",
    "    VALUES(23453, 6127, 3158, 'Lagos Nigeria'), (43213, 5692, 2561, 'Ottawa, Canada'), (12345, 435, 3158, 'Lagos Nigeria');\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>name</th>\n",
       "        <th>billing_address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>435</td>\n",
       "        <td>Omar</td>\n",
       "        <td>Berlin, Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>5692</td>\n",
       "        <td>Stuart</td>\n",
       "        <td>Dover, UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6127</td>\n",
       "        <td>Vidhya</td>\n",
       "        <td>Mumbai, India</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(435, 'Omar', 'Berlin, Germany'),\n",
       " (5692, 'Stuart', 'Dover, UK'),\n",
       " (6127, 'Vidhya', 'Mumbai, India')]"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM customers;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>price</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>89</td>\n",
       "        <td>16.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>103</td>\n",
       "        <td>6.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2561</td>\n",
       "        <td>21.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>3158</td>\n",
       "        <td>101.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>4028</td>\n",
       "        <td>35.5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(89, 16.95), (103, 6.95), (2561, 21.35), (3158, 101.99), (4028, 35.5)]"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM products;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62533</td>\n",
       "        <td>435</td>\n",
       "        <td>103</td>\n",
       "        <td>Munich, Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62353</td>\n",
       "        <td>435</td>\n",
       "        <td>4028</td>\n",
       "        <td>Tunis, Tunisia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64598</td>\n",
       "        <td>5692</td>\n",
       "        <td>103</td>\n",
       "        <td>Dover, UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65271</td>\n",
       "        <td>5692</td>\n",
       "        <td>103</td>\n",
       "        <td>Dover, UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23453</td>\n",
       "        <td>6127</td>\n",
       "        <td>3158</td>\n",
       "        <td>Lagos Nigeria</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>43213</td>\n",
       "        <td>5692</td>\n",
       "        <td>2561</td>\n",
       "        <td>Ottawa, Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>12345</td>\n",
       "        <td>435</td>\n",
       "        <td>3158</td>\n",
       "        <td>Lagos Nigeria</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(62533, 435, 103, 'Munich, Germany'),\n",
       " (62353, 435, 4028, 'Tunis, Tunisia'),\n",
       " (64598, 5692, 103, 'Dover, UK'),\n",
       " (65271, 5692, 103, 'Dover, UK'),\n",
       " (23453, 6127, 3158, 'Lagos Nigeria'),\n",
       " (43213, 5692, 2561, 'Ottawa, Canada'),\n",
       " (12345, 435, 3158, 'Lagos Nigeria')]"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT * FROM orders;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Just like head/tail in pandas, we can limit how much gets printed from SQL table by using the limit command"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * sqlite:///testdb.sqlite#\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>id</th>\n",
       "        <th>customer_id</th>\n",
       "        <th>product_id</th>\n",
       "        <th>delivery_address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62533</td>\n",
       "        <td>435</td>\n",
       "        <td>103</td>\n",
       "        <td>Munich, Germany</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>62353</td>\n",
       "        <td>435</td>\n",
       "        <td>4028</td>\n",
       "        <td>Tunis, Tunisia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>64598</td>\n",
       "        <td>5692</td>\n",
       "        <td>103</td>\n",
       "        <td>Dover, UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>65271</td>\n",
       "        <td>5692</td>\n",
       "        <td>103</td>\n",
       "        <td>Dover, UK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>23453</td>\n",
       "        <td>6127</td>\n",
       "        <td>3158</td>\n",
       "        <td>Lagos Nigeria</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(62533, 435, 103, 'Munich, Germany'),\n",
       " (62353, 435, 4028, 'Tunis, Tunisia'),\n",
       " (64598, 5692, 103, 'Dover, UK'),\n",
       " (65271, 5692, 103, 'Dover, UK'),\n",
       " (23453, 6127, 3158, 'Lagos Nigeria')]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM orders\n",
    "LIMIT 5;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6.9 64-bit",
   "language": "python",
   "name": "python36964bit83539e9507bf4304ba502a0f8acf9217"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
